# encoding=utf8
# !/usr/bin/env python

"""
Fixes a MySQL dump made with the right format so it can be directly
imported to a new PostgreSQL database.

Dump using:
mysqldump --opt --compatible=postgresql --default-character-set=utf8 -d databasename -r dumpfile.sql -u root -p
"""

import re
import sys
import os
import time
import subprocess
import subprocess as sp
import threading
from threading import Timer
import time
import datetime


reload(sys)
sys.setdefaultencoding('utf8')


DATABASELIST = ["i2fa","iapps","icatalog","icharge","ibase","idevops","ilifecycle","ilog","imesh","imonitor","information_schema","iorchestrator","ipaas","iphymachine","irecovery","ireport","iresource","iresource_common","isofthouse","itask","itrouble","iworkflow"]
#DATABASELIST = ["itask"]
MYSQLHOST = "galera"
MYSQLPORT = "3306"
MYSQLUSER = "root"
MYSQLPASSWORD = "123456a?"
KINGBASEPATH = "/var/lib/data/db/bin/"
KINGBASEDATAPATH = "/var/lib/data/db/data"
KINGBASEUSER = "kingbase"
TABLESPACE = ""
CREATE_DATABASE = "CREATE DATABADE IF NOT EXISTS TEST3 OWNER SYSTEM"

CREATETABLESPACE = "CREATE TABLESPACE INCLOUD OWNER SYSTEM LOCATION '/home/test/userdata/data/incloud';"
def getHostName():
    cmd = "echo $HOSTNAME"
    popen = subprocess.Popen(cmd,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
    hostname = popen.stdout.read().strip()
    return hostname

def getDatabases():
    cmd = """/usr/local/mysql/bin/mysql -e "show databases;"  -h %s -P %s -u%s -p%s | grep -Ev "Database|information_schema|mysql|performance_schema" """%(MYSQLHOST,MYSQLPORT,MYSQLUSER,MYSQLPASSWORD)
    print cmd
    popen = subprocess.Popen(cmd,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
    return popen.stdout.readlines()

def getTables(database):
    cmd = """/usr/local/mysql/bin/mysql -e "use %s; show tables;"  -h %s -P %s -u%s -p%s | grep -Ev "Database|information_schema|mysql|performance_schema" """%(database,MYSQLHOST,MYSQLPORT,MYSQLUSER,MYSQLPASSWORD)
    print cmd
    popen = subprocess.Popen(cmd,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
    return popen.stdout.readlines()

def exportFromMYSQL(database):
    cmd = "/usr/local/mysql/bin/mysqldump  -h 100.2.29.121 -P 33306 --default-character-set=utf8 --hex-blob --compatible=postgresql -uroot -p123456a? --databases %s> /tmp/%spg.sql"%(database,database)
    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --add-drop-table --default-character-set=utf8 --no-data --compatible=postgresql -uroot -p123456a? --databases %s> /tmp/%spg.sql"%(MYSQLHOST,MYSQLPORT,database,database)
    os.system(cmd)

def exportDataFromMYSQL(database):
    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -uroot -p123456a? --databases %s> /tmp/%spgdata.sql"%(MYSQLHOST,MYSQLPORT,database,database)
#    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --hex-blob --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -uroot -p123456a? --databases %s> /tmp/%spgdata.sql"%(MYSQLHOST,MYSQLPORT,database,database)
    os.system(cmd)
    
def exportDataFromMYSQLByTable(database,tablename):
    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --extended-insert --skip-comments --skip-dump-date -uroot -p123456a? --databases %s --tables %s > /tmp/%s_%s_pgdata.sql"%(MYSQLHOST,MYSQLPORT,database,tablename,database,tablename)
    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -uroot -p123456a? --databases %s --tables %s > /tmp/%s_%s_pgdata.sql"%(MYSQLHOST,MYSQLPORT,database,tablename,database,tablename)
#    cmd = "/usr/local/mysql/bin/mysqldump  -h %s -P %s --hex-blob --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -uroot -p123456a? --databases %s> /tmp/%spgdata.sql"%(MYSQLHOST,MYSQLPORT,database,database)
    os.system(cmd)
    
def importToKDB(database):
    cmd = '%sksql -U SYSTEM -W 123456 -c "DROP DATABASE IF EXISTS %s" -d SAMPLES'%(KINGBASEPATH,database)
    print cmd
    os.system(cmd)
    LOCATION = '%s/%s'%(KINGBASEDATAPATH,database)
    hostname = getHostName().rsplit("kingbase",1)[0]
    hostname1 = "%skingbase-1.%sheadless.incloud.svc.cluster.local"%(hostname,hostname)
    cmd = 'ssh -o StrictHostKeyChecking=no -l root -T %s "mkdir -p %s"'%(hostname1,LOCATION)
    print cmd
    os.system(cmd)
    cmd = 'ssh -o StrictHostKeyChecking=no -l root -T %s "chown -R %s:%s %s"'%(hostname1,KINGBASEUSER,KINGBASEUSER,LOCATION)
    print cmd
    os.system(cmd)
    if not os.path.exists(LOCATION):
        os.makedirs(LOCATION)
        os.system("chown -R %s:%s %s"%(KINGBASEUSER,KINGBASEUSER,LOCATION))
    cmd = '%sksql -U SYSTEM -W 123456 -c "CREATE TABLESPACE %s OWNER SYSTEM LOCATION \'%s\';"  -d SAMPLES'%(KINGBASEPATH,database,LOCATION)
    print cmd
    os.system(cmd)
    cmd = '%sksql -U SYSTEM -W 123456 -c "CREATE DATABASE %s OWNER SYSTEM TABLESPACE %s" -d SAMPLES'%(KINGBASEPATH,database,database)
    print cmd
#    time.sleep(3)
    os.system(cmd)
    cmd = "%sksql -U SYSTEM -W 123456 -d %s -f /tmp/%skdb.sql > /tmp/%stable.log"%(KINGBASEPATH,database.upper(),database,database)
    cmd = "cat /tmp/%skdb.sql | %sksql -U SYSTEM -W 123456 -d %s > /tmp/%stable.log"%(database,KINGBASEPATH,database.upper(),database)
    print cmd
    time.sleep(2)
    os.system(cmd)

def importDataToKDB(database):
    cmd = "%sksql -U SYSTEM -W 123456 -d %s -f /tmp/%skdbdata.sql > /tmp/%sdata.log"%(KINGBASEPATH,database.upper(),database,database)
    cmd = "cat /tmp/%skdbdata.sql | tail -n +5 | %sksql -U SYSTEM -W 123456 -d %s > /tmp/%sdata.log"%(database,KINGBASEPATH,database.upper(),database)
    print cmd
    os.system(cmd)
    
def changeHex2Str(input_filename, output_filename):
    cmd = r"""sed "s/0x\([0-9A-F]*\)/E'\\\\\\\\x\1'::bytea/g" %s > %s"""%(input_filename, output_filename)
    print cmd
    os.system(cmd)

def converterData(input_filename, output_filename):
    f=open(input_filename,"r")
    content = f.readlines()
    f.close()
    f=open(output_filename,"w")
    for line in content: 
        if line.startswith("USE ") or line.startswith("/*") or line.startswith('INSERT INTO "t_ba_sysconfig_picture"') or line.startswith('INSERT INTO "tb_cert_store"') or line.startswith("CREATE DATABASE") or not line: #
            continue
#        if line.startswith('INSERT INTO "t_ba_sysconfig_param"') or line.startswith('INSERT INTO "t_ba_user_role"'):
#            line = line.replace("''","' '")
        line = line.replace("'\\0'","'0'").replace("'" + chr(1) + "'","'1'").replace("''","' '").replace("'0000-00-00 00:00:00'","''").replace("\\'","''")
        f.write(line)
    f.close()

def parse(input_filename, output_filename):
    "Feed it a file, and it'll output a fixed one"
    print (input_filename, output_filename)
    # State storage
    if input_filename == "-":
        num_lines = -1
    else:
        num_lines = int(subprocess.check_output(["wc", "-l", input_filename]).strip().split()[0])
    tables = {}
    current_table = None
    creation_lines = []
    cast_lines = []
    index_lines = []
    data_lines = []
    comment_line = []
    num_inserts = 0
    started = time.time()
    primary_key = None

    # Open output file and write header. Logging file handle will be stdout
    # unless we're writing output to stdout, in which case NO PROGRESS FOR YOU.
    if output_filename == "-":
        output = sys.stdout
        logging = open(os.devnull, "w")
    else:
        output = open(output_filename, "w")
        logging = sys.stdout

    if input_filename == "-":
        input_fh = sys.stdin
    else:
        input_fh = open(input_filename)

    output.write("-- Converted by db_converter\n")
    output.write("START TRANSACTION;\n")
    # output.write("SET standard_conforming_strings=off;\n")
    # output.write("SET escape_string_warning=off;\n")
    # output.write("SET CONSTRAINTS ALL DEFERRED;\n\n")

    for i, line in enumerate(input_fh):
        time_taken = time.time() - started
        percentage_done = (i + 1) / float(num_lines)
        secs_left = (time_taken / percentage_done) - time_taken
        logging.write("\rLine %i (of %s: %.2f%%) [%s tables] [%s inserts] [ETA: %i min %i sec]" % (
            i + 1,
            num_lines,
            ((i + 1) / float(num_lines)) * 100,
            len(tables),
            len(data_lines),
            secs_left // 60,
            secs_left % 60,
        ))
        logging.flush()
        line = line.decode("utf8").strip().replace(r"\\", "WUBWUBREALSLASHWUB").replace(r"\'", "''").replace("WUBWUBREALSLASHWUB", r"\\")
        # Ignore comment lines
        if line.startswith("--") or line.startswith("/*") or line.startswith("LOCK TABLES") or line.startswith("UNLOCK TABLES") or not line:
            continue

        # Outside of anything handling
        if current_table is None:
            # Start of a table creation statement?
            if line.startswith("CREATE TABLE"):
                current_table = line.split('"')[1].lower()
                tables[current_table] = {"columns": []}
                creation_lines = []
            # INSERT INTO start is data
            elif line.startswith("INSERT INTO"):
                data_lines.append(u"%s\n" % line)
            # DROP TABLE start is data
            elif line.startswith("DROP TABLE"):
                output.write(u"%s\n" % line.replace(";"," cascade;"))
            else:
                print "\n ! Unknown line in main body: %s" % line

        # Inside-create-statement handling
        else:
            # Is it a column?
            # " start is column
            if line.startswith('"'):
                useless, name, definition = line.strip(",").split('"', 2)
                name = name.lower()
                try:
                    type, extra = definition.strip().split(" ", 1)

                except ValueError:
                    type = definition.strip()
                    extra = ""
                extra = re.sub("CHARACTER SET [\w\d]+\s*", "", extra)
                extra = re.sub("COLLATE [\w\d]+\s*", "", extra.replace("'0000-00-00 00:00:00'", "NULL"))
                if extra.find("COMMENT '") > -1:
                    pattern = re.compile("COMMENT '(.*)'")
                    comment_line.append(u"COMMENT ON COLUMN \"%s\".\"%s\" is '%s'" % (current_table, name, pattern.findall(extra)[0]))
                    extra = re.sub("COMMENT '.*'", "", extra)

                # See if it needs type conversion
                final_type = None
                if type.startswith("tinyint("):
                    type = "smallint"
                elif type.startswith("int(") and extra.startswith("unsigned"):
                    type = "bigint"
                elif type.startswith("int("):
                    type = "integer"
                elif type.startswith("smallint(") and extra.startswith("unsigned"):
                    type = "integer"
                elif type.startswith("smallint("):
                    type = "smallint"
                elif type.startswith("mediumint("):
                    type = "integer"
                elif type.startswith("bigint("):
                    type = "bigint"
                elif type.startswith("year"):
                    type = "integer"

                elif type == "longtext":
                    type = "text"
                elif type == "mediumtext":
                    type = "text"
                elif type == "tinytext":
                    type = "text"
                elif type.startswith("varchar(") or type.startswith("varbinary("):
                    size = int(type.split("(")[1].split(")")[0])
                    type = "varchar(%s)" % (size * 2)

                elif type == "datetime":
                    type = "timestamp without time zone"
                elif type == "timestamp":
                    type = "timestamp with time zone"

                elif type.startswith("double"):
                    type = "double precision"
                elif type.startswith("float"):
                    type = "real"
                elif type.endswith("blob"):
                    type = "bytea"
                    type = "clob"
                elif type.endswith("binary"):
                    type = "bytea"
                elif type.startswith("enum(") or type.startswith("set("):
                    type = "varchar"

                elif type.startswith("linestring"):
                    type = "path"
                elif type.startswith("point"):
                    type = "point"

                extra = extra.replace("unsigned", "").replace("current_timestamp()", "current_timestamp").replace("zerofill", "")

                if final_type:
                    cast_lines.append(
                        "ALTER TABLE \"%s\" ALTER COLUMN \"%s\" DROP DEFAULT, ALTER COLUMN \"%s\" TYPE %s USING CAST(\"%s\" as %s)" % (current_table, name, name, final_type, name, final_type))
                creation_lines.append('"%s" %s %s' % (name, type, extra))
                tables[current_table]['columns'].append((name, type, extra))
            # Is it a constraint or something?
            elif line.startswith("PRIMARY KEY"):
                creation_lines.append(line.rstrip(",").lower())
                primary_key = line.split("(")[1].split(")")[0].lower()
            elif line.startswith("UNIQUE KEY"):
                creation_lines.append("UNIQUE (%s)" % line.split("(")[1].split(")")[0].lower())
            elif line.startswith("KEY"):
                index_lines.append("DROP INDEX IF EXISTS  %s" % ( line.split(" ")[1]))
                index_lines.append("CREATE INDEX %s on \"%s\" (%s)" % (line.split(" ")[1], current_table, line.split("(")[1].split(")")[0].lower()))
            # Is it the end of the table?
            elif line == ");" or line == ")":
                output.write("CREATE TABLE \"%s\" (\n" % current_table)
                for i, line in enumerate(creation_lines):
                    output.write("    %s%s\n" % (line, "," if i != (len(creation_lines) - 1) else ""))
                output.write(');\n')
                output.write("\n-- Add Comment --\n")
                for line in comment_line:
                    output.write(u"%s;\n" % line)
                output.write('\n\n')
#                if primary_key is not None:
#                    output.write(') distributed by (%s);\n\n' % primary_key)
#                else:
#                    output.write(') distributed by ();\n\n')
                comment_line = []
                current_table = None
                primary_key = None
            # ???
            else:
                print "\n ! Unknown line inside table creation: %s" % line

    # Finish file
#    # Write index out
#    output.write("START TRANSACTION;\n")
#    output.write("\n-- Index --\n")
#    for line in index_lines:
#        output.write("%s;\n" % line)
#    output.write("\n")
#    output.write("COMMIT;\n")
    # Finish file

#    output.write("START TRANSACTION;\n")
#    output.write("\n-- Post-data save --\n")
#    for line in data_lines:
#        output.write(u"%s\n" % line)
    output.write("COMMIT;\n\n")

def exec_cmd(cmd):
    result = sp.call([cmd], shell=True)
    
def SynData(execHout = "22"):
    hostname = getHostName()
    print hostname
    now = datetime.datetime.now()
    ts = now.strftime('%H:%M:%S')
    print ts
    os.system("echo %s >> /tmp/mysql2kdb.log"%ts)
    if hostname.endswith("kingbase-0"):
        Hour = now.strftime('%H')
        if Hour == execHout:
            DATABASELIST = getDatabases()
            print DATABASELIST
            for database in DATABASELIST:
                database = database.strip()
                print ("begin %s"%database)
                exportFromMYSQL(database)
                parse("/tmp/%spg.sql"%database, "/tmp/%skdb.sql"%database)
                importToKDB(database)
            for database in DATABASELIST:
                database = database.strip()
                print ("begin %s"%database)
                TABLES = getTables(database)
                for tablename in TABLES:
                    tablename = tablename.strip()
                    exportDataFromMYSQLByTable(database,tablename)
                    converterData("/tmp/%s_%s_pgdata.sql"%(database,tablename), "/tmp/%s_%s_kdbdata.sql"%(database,tablename))
        #        exportDataFromMYSQL(database)
        #        changeHex2Str("/tmp/%spgdata.sql"%database, "/tmp/%skdbdata.sql"%database)
        #        importDataToKDB(database)
        #        converterData("/tmp/%spgdata.sql"%database, "/tmp/%skdbdata.sql"%database)
                    cmd = "cat /tmp/%s_%s_kdbdata.sql | tail -n +1 | %sksql -U SYSTEM -W 123456 -d %s > /tmp/%sdata.log"%(database,tablename,KINGBASEPATH,database.upper(),database)
                    print cmd
                    os.system(cmd)
#                my_thread = threading.Thread(target=exec_cmd, args=(cmd,))
#                my_thread.start()
        Timer(60 * 60, SynData).start()
def test():
    DATABASELIST = getDatabases()
    print DATABASELIST
    DATABASELIST = ["ibase"]
    for database in DATABASELIST:
        database = database.strip()
        print ("begin %s"%database)
        exportFromMYSQL(database)
        parse("/tmp/%spg.sql"%database, "/tmp/%skdb.sql"%database)
        importToKDB(database)
        time.sleep(2)
    for database in DATABASELIST:
        database = database.strip()
        print ("begin %s"%database)
        TABLES = getTables(database)
        print TABLES
        for tablename in TABLES:
            tablename = tablename.strip()
            exportDataFromMYSQLByTable(database,tablename)
            converterData("/tmp/%s_%s_pgdata.sql"%(database,tablename), "/tmp/%s_%s_kdbdata.sql"%(database,tablename))
            cmd = '%sksql -U SYSTEM -W 123456 -c \'DELETE FROM \"%s\";\'  -d %s'%(KINGBASEPATH,tablename,database.upper())
            print cmd
            os.system(cmd)
            cmd = "cat /tmp/%s_%s_kdbdata.sql | tail -n +1 | %sksql -U SYSTEM -W 123456 -d %s > /tmp/%sdata.log"%(database,tablename,KINGBASEPATH,database.upper(),database)
            print cmd
            os.system(cmd)
            time.sleep(1)
if __name__ == "__main__":
    test()
#    converterData(sys.argv[1], sys.argv[2])
#    parse(sys.argv[1], sys.argv[2])
#    if len(sys.argv) > 1:
#        SynData(sys.argv[1])
#    else:
#        SynData()
